package com.zoulj.util;

import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONObject;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.streaming.SXSSFCell;
import org.apache.poi.xssf.streaming.SXSSFRow;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.BufferedInputStream;
import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.net.URLEncoder;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

/**
 * @author leonard
 * @Description
 * @date 2021-06-02 15:59
 */
@Slf4j
public class ExcelUtil {

    /**
     * @param workbook
     * @param byteArrayOutputStream new ByteArrayOutputStream()即可
     * @param response
     * @param returnName
     * @throws IOException
     */
    public static void download(SXSSFWorkbook workbook, ByteArrayOutputStream byteArrayOutputStream,
                                HttpServletResponse response, String returnName) throws IOException {
        BufferedInputStream bis = null;
        ServletOutputStream outputstream = null;
        try {
            returnName = URLEncoder.encode(returnName, "UTF-8").replaceAll("\\+", "%20");
            outputstream = response.getOutputStream();
            response.setContentType("application/octet-stream");
            response.setHeader("content-disposition", "attachment;filename=" + returnName);
            response.setCharacterEncoding("UTF-8");
            //获取导出文件大小
            if (null != workbook)
                workbook.write(byteArrayOutputStream);
            response.setContentLength(byteArrayOutputStream.size());
            byte[] content = byteArrayOutputStream.toByteArray();
            //需指定content-length,否则文件损坏
            bis = new BufferedInputStream(new ByteArrayInputStream(content));
            response.setHeader("Content-Length", String.valueOf(bis.available()));
            outputstream.write(content);
            byteArrayOutputStream.writeTo(outputstream);
            outputstream.flush();
        } catch (Exception e) {
            log.info("error:{}", e);
        } finally {
            if (bis != null) {
                bis.close();
            }
            if (byteArrayOutputStream != null) {
                byteArrayOutputStream.close();
            }
            if (outputstream != null) {
                outputstream.close();
            }
            if (null != workbook) {
                workbook.dispose();
            }
        }
    }

    public static List<String[]> readExcel(MultipartFile multipartFile) {
        List<String[]> result = new ArrayList<String[]>();
        SXSSFWorkbook wb = new SXSSFWorkbook();
        SXSSFSheet sheet = wb.getSheetAt(0);//获取第一张表
        int begin = sheet.getFirstRowNum(); //第一个为表头.
        SXSSFRow firstRow = sheet.getRow(begin);
        int cellTotal = firstRow.getPhysicalNumberOfCells();
        int rows = sheet.getLastRowNum();
        for (int i = begin + 1; i <= rows; i++) {
            SXSSFRow row = sheet.getRow(i);
            if (null != row) {
                String[] cells = new String[cellTotal];
                for (int k = 0; k < cellTotal; k++) {
                    SXSSFCell cell = row.getCell(k);
                    if (cell == null) {
                        continue;
                    }
                    String strCell = "";
                    switch (cell.getCellType()) {
                        case STRING:
                            strCell = cell.getStringCellValue();
                            break;
                        case NUMERIC:
                            Double value = (Double) cell.getNumericCellValue(); //强制为double类型.
                            strCell = String.valueOf(cell.getNumericCellValue());
                            break;
                        case BOOLEAN:
                            strCell = String.valueOf(cell.getBooleanCellValue());
                            break;
                        case BLANK:
                            strCell = "";
                            break;
                        default:
                            strCell = "";
                    }
                    cells[k] = strCell;
                }
                result.add(cells);
            }
        }
        return result;
    }

    /**
     * @param sheetTitle
     * @param headerTitle excel的表头字段
     * @param headerField List bo的字段集合
     *                    headerTitle==headerField
     * @param datalist    bo的集合对象
     * @param response
     * @param fileName
     */
    public static void exportExcel(String sheetTitle, List<String> headerTitle, List<String> headerField,
                                   List datalist, HttpServletResponse response, String fileName) throws IOException {
        SXSSFWorkbook workbook = new SXSSFWorkbook();
        SXSSFSheet sheet = workbook.createSheet(sheetTitle);
        // 产生表格标题行
        SXSSFRow row = sheet.createRow(0);
        int headerlen = headerTitle.size();
        StringBuffer sb = new StringBuffer();
        for (int index = 0; index < headerlen; index++) {
            SXSSFCell cell = row.createCell(index);
            cell.setCellStyle(getTableHeaderStyle(workbook));
            cell.setCellValue(headerTitle.get(index));
        }
        //便利集合数据，根据header产生数据行
        for (int index = 1; index <= datalist.size(); index++) {
            row = sheet.createRow(index);
            String oStr = JSON.toJSONString(datalist.get(index - 1));
            JSONObject o = JSON.parseObject(oStr);
            for (int id = 0; id < headerlen; id++) {
                SXSSFCell cell = row.createCell(id);
                cell.setCellStyle(getContentCellStyle(workbook));
                String fieldName = headerField.get(id);
                try {
                    Object value = o.get(fieldName);
                    // 判断值的类型后进行强制类型转换
                    String textValue = "";
                    if (value instanceof Date) {
                        Date date = (Date) value;
                        textValue = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(date);
                    } else {// 其它当作字符串处理
                        textValue = (value != null ? value.toString() : "");
                    }
                    cell.setCellValue(textValue);
                } catch (SecurityException e) {
                    log.info("error " + e);
                }
            }
        }
        download(workbook, new ByteArrayOutputStream(), response, fileName);
    }


    private static CellStyle getContentCellStyle(SXSSFWorkbook workbook) {
        CellStyle cellStyle = workbook.createCellStyle();
        //边框设置
        BorderStyle thin = BorderStyle.THIN;
        cellStyle.setBorderBottom(thin);
        cellStyle.setBorderLeft(thin);
        cellStyle.setBorderTop(thin);
        cellStyle.setBorderRight(thin);

        Font headerFont1 = workbook.createFont(); // 创建字体样式
        headerFont1.setFontName("宋体"); // 设置字体类型
        headerFont1.setFontHeightInPoints((short) 11); // 设置字体大小
        cellStyle.setFont(headerFont1); // 为标题样式设置字体样式
        return cellStyle;
    }

    private static CellStyle getBigHeaderCellStyle(SXSSFWorkbook workbook) {
        CellStyle cellStyle = workbook.createCellStyle();
        cellStyle.setAlignment(HorizontalAlignment.CENTER);//水平居中
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);//垂直对齐

        //边框设置
        BorderStyle thin = BorderStyle.THIN;
        cellStyle.setBorderBottom(thin);
        cellStyle.setBorderLeft(thin);
        cellStyle.setBorderTop(thin);
        cellStyle.setBorderRight(thin);

        Font headerFont1 = workbook.createFont(); // 创建字体样式
        headerFont1.setBold(true); //字体加粗
        headerFont1.setFontName("宋体"); // 设置字体类型
        headerFont1.setFontHeightInPoints((short) 10); // 设置字体大小
        cellStyle.setFont(headerFont1); // 为标题样式设置字体样式
        //启动单元格内换行
        cellStyle.setWrapText(true);
        return cellStyle;
    }

    private static CellStyle getTableHeaderStyle(SXSSFWorkbook workbook) {
        CellStyle cellStyle = workbook.createCellStyle();
        //边框设置
        BorderStyle thin = BorderStyle.THIN;
        cellStyle.setBorderBottom(thin);
        cellStyle.setBorderLeft(thin);
        cellStyle.setBorderTop(thin);
        cellStyle.setBorderRight(thin);
        Font headerFont1 = workbook.createFont(); // 创建字体样式
        headerFont1.setFontName("宋体"); // 设置字体类型
        headerFont1.setFontHeightInPoints((short) 11); // 设置字体大小
        cellStyle.setFont(headerFont1); // 为标题样式设置字体样式

        cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        cellStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());

        return cellStyle;
    }
}
